/**
 * @date：2014-5-28-下午11:39:36
 * @author: James zhang
 * @Description
 * @version: 1.0.0
 */
package com.zx.platform.utils.report;

import com.zx.map.utils.SUConstants;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

/**
 * @author: James zhang
 * @Description
 */
public class ReportUtil {

	final static String filePath = "/home/webservice/logs";
	public static final int MAXLINE = 60000;
	// ---默认提供了一些格式化;
	public static final DecimalFormat Decimal_Money_glob = new DecimalFormat(
			",##0.00");
	public static final DecimalFormat Decimal_Money_chn = new DecimalFormat(
			"#0.00");
	public static final DecimalFormat Decimal_Money_percent = new DecimalFormat(
			"#0.00%");

	public static final SimpleDateFormat Date_Time = new SimpleDateFormat(
			"yyyyMMdd hh:mm:ss");
	public static final SimpleDateFormat Date_Time3 = new SimpleDateFormat(
			"yyyyMMddhhmmSS"); // 毫秒
	public static final SimpleDateFormat Date_Time2 = new SimpleDateFormat(
			"yyyy-MM-dd HH:mm:ss");
	public static final SimpleDateFormat Date_Only = new SimpleDateFormat(
			"yyyyMMdd");
	public static final SimpleDateFormat Date_cro = new SimpleDateFormat(
			"yyyy-MM-dd");

	/**
	 * 导出excel
	 * @param header
	 * @param list
	 * @param filePath
	 * @param fileName
	 * @return
	 * @throws Exception
	 */
	public static String exportReportXLS(String[] header,
			List<? extends Object> list, String filePath, String fileName,
			Map<Integer, Object> formatAttr, String[] filterNSeq,
			HttpServletResponse response) throws Exception {
		if (StringUtils.isBlank(filePath)) {
			filePath = ReportUtil.filePath;
		}
		if (list.size() > 65530) {
			return SUConstants.DOWNLOAD_MAX_SIZE;
		}
		fileName = StringUtils.isBlank(fileName) ? Date_Time3
				.format(new Date()) : fileName + Date_Time3.format(new Date());
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet();
		HSSFCellStyle style = getCellHeaderStyle(wb);
		HSSFCellStyle styledata = getCellDataStyle(wb);
		HSSFRow headerRow = sheet.createRow(0);
		headerRow.setHeight((short) 250);
		// header 赋值
		HSSFCell cell = null;
		if (header != null) {
			for (int i = 0; i < header.length; i++) {
				cell = headerRow.createCell(i);
				cell.setCellStyle(style);
				cell.setCellValue(header[i]);
			}
		}
		// Data赋值list
		HSSFRow dataRow = null;
		Object[] obs = null;
		Object ob = null;
		if (list != null && list.size() > 0) {
			int len = 0;
			if (filterNSeq != null) {
				len = filterNSeq.length;
			} else {
				len = ReportBeanUtils.getBeanLenh(list.get(0));
			}
			for (int i = 0; i < list.size(); i++) {
				dataRow = sheet.createRow(i + 1);
				if (filterNSeq != null) {
					obs = ReportBeanUtils.convertBeanByFieldName(list.get(i),
							filterNSeq);
				} else {
					obs = ReportBeanUtils.convertBean(list.get(i));
				}
				for (int j = 0; j < len; j++) {
					cell = dataRow.createCell(j);
					cell.setCellStyle(styledata);
					ob = (obs[j] == null ? "" : obs[j]);
					boolean exisFormat = formatAttr.containsKey(j + 1);
					if (ob instanceof Date) {
						if (exisFormat) {
							ob = ((SimpleDateFormat) (formatAttr.get(j + 1)))
									.format(ob);
						}
						cell.setCellValue(ob.toString());
					} else if (ob instanceof Double || ob instanceof BigDecimal) {
						if (exisFormat) {
							ob = ((DecimalFormat) (formatAttr.get(j + 1)))
									.format(ob);
							try {
								ob = new Double(ob.toString());
								cell.setCellValue((Double) ob);
								cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
							} catch (Exception e) {
								// 没法转double 说明被 format成了 123,233.09 或者 34%
								// 等无法double化了..
								cell.setCellValue(ob.toString());
							}
						} else {
							cell.setCellValue((Double) ob);
							cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
						}
					} else if (ob instanceof Integer) {
						cell.setCellValue((Integer) ob);
						cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
					} else {
						cell.setCellValue(ob.toString());
					}

				}
			}
		}
		String fileTotalPath = filePath.trim() + "/" + fileName + ".xls";

		String fn = fileName + ".xls";
		FileOutputStream fo = new FileOutputStream(fileTotalPath);
		wb.write(fo);
		fo.close();
		return fileTotalPath + "#" + fn;

	}

	/**
	 * list的model加字段就死翘翘了
	 * @param header
	 * @param list
	 * @param filePath
	 * @param fileName
	 * @param formatAttr
	 * @param filterNSeq
	 * @param response
	 * @return
	 * @throws Exception
	 */
	@Deprecated
	public static String exportReport(String[] header,
	                                  List<? extends Object> list, String filePath, String fileName,
	                                  Map<Integer, Object> formatAttr, int[] filterNSeq,
	                                  HttpServletResponse response) throws Exception {
		if (StringUtils.isBlank(filePath)) {
			filePath = ReportUtil.filePath;
		}
		if (list.size() > 65530) {
			return null;
		}
		fileName = StringUtils.isBlank(fileName) ? Date_Time3
				.format(new Date()) : fileName + Date_Time3.format(new Date());
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet();
		HSSFCellStyle style = getCellHeaderStyle(wb);
		HSSFCellStyle styledata = getCellDataStyle(wb);
		HSSFRow headerRow = sheet.createRow(0);
		headerRow.setHeight((short) 250);
		// header 赋值
		HSSFCell cell = null;
		if (header != null) {
			for (int i = 0; i < header.length; i++) {
				cell = headerRow.createCell(i);
				cell.setCellStyle(style);
				cell.setCellValue(header[i]);
			}
		}
		// Data赋值list
		HSSFRow dataRow = null;
		Object[] obs = null;
		Object ob = null;
		if (list != null && list.size() > 0) {
			int len = 0;
			if (filterNSeq != null) {
				len = filterNSeq.length;
			} else {
				len = ReportBeanUtils.getBeanLenh(list.get(0));
			}
			for (int i = 0; i < list.size(); i++) {
				dataRow = sheet.createRow(i + 1);
				if (filterNSeq != null) {
					obs = ReportBeanUtils.convertBeanFilterNSeq(list.get(i),
							filterNSeq);
				} else {
					obs = ReportBeanUtils.convertBean(list.get(i));
				}
				for (int j = 0; j < len; j++) {
					cell = dataRow.createCell(j);
					cell.setCellStyle(styledata);
					ob = (obs[j] == null ? "" : obs[j]);
					boolean exisFormat = formatAttr.containsKey(j + 1);
					if (ob instanceof Date) {
						if (exisFormat) {
							ob = ((SimpleDateFormat) (formatAttr.get(j + 1)))
									.format(ob);
						}
						cell.setCellValue(ob.toString());
					} else if (ob instanceof Double || ob instanceof BigDecimal) {
						if (exisFormat) {
							ob = ((DecimalFormat) (formatAttr.get(j + 1)))
									.format(ob);
							try {
								ob = new Double(ob.toString());
								cell.setCellValue((Double) ob);
								cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
							} catch (Exception e) {
								// 没法转double 说明被 format成了 123,233.09 或者 34%
								// 等无法double化了..
								cell.setCellValue(ob.toString());
							}
						} else {
							cell.setCellValue((Double) ob);
							cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
						}
					} else if (ob instanceof Integer) {
						cell.setCellValue((Integer) ob);
						cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
					} else {
						cell.setCellValue(ob.toString());
					}

				}
			}
		}
		String fileTotalPath = filePath.trim() + "/" + fileName + ".xls";

		String fn = fileName + ".xls";
		FileOutputStream fo = new FileOutputStream(fileTotalPath);
		wb.write(fo);
		fo.close();
		return fileTotalPath + "#" + fn;

	}

	public static <T> String readXls(File f, List<T> list, Class<T> cls)
			throws Exception {
		InputStream input = new FileInputStream(f);
		POIFSFileSystem fs = new POIFSFileSystem(input);
		HSSFWorkbook wb = new HSSFWorkbook(fs);
		HSSFSheet sheet = wb.getSheetAt(0);

		// 根据第一行获取必要信息
		int totalRows = sheet.getPhysicalNumberOfRows();
		int cellLeg = 0;
		if (totalRows == 0) {
			return "上传内容为空";
		} else {
			cellLeg = sheet.getRow(0).getPhysicalNumberOfCells();
			if ((cls.getDeclaredFields()).length < cellLeg) {
				return "上传列数不够";
			}
		}
		// 删除第一行//因为第一行是header 嘿嘿
		sheet.removeRow(sheet.getRow(0));
		Iterator<?> rows = sheet.rowIterator();
		HSSFRow row = null;
		HSSFCell cell = null;
		int celllength = 0;
		while (rows.hasNext()) {
			row = (HSSFRow) rows.next();
			Object bean = cls.newInstance();
			celllength = cellLeg;
			Object[] ob = new Object[cellLeg];
			for (int i = 0; i < celllength; i++) {
				cell = (HSSFCell) row.getCell(i);
				if (cell == null) {
					ob[i] = null;
					continue;
				}
				switch (cell.getCellType()) {
				case HSSFCell.CELL_TYPE_NUMERIC: // 数字要格外处理

					if (HSSFDateUtil.isCellDateFormatted(cell)) {
						ob[i] = HSSFDateUtil.getJavaDate(cell
								.getNumericCellValue());
					} else {

						ob[i] = cell.getNumericCellValue();
					}
					break;
				case HSSFCell.CELL_TYPE_STRING: // 字符串
					ob[i] = cell.getStringCellValue();
					break;
				case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
					ob[i] = cell.getBooleanCellValue();
					break;
				case HSSFCell.CELL_TYPE_FORMULA: // 公式
					ob[i] = cell.getCellFormula();
					break;
				case HSSFCell.CELL_TYPE_BLANK: // 空值
					System.out.println(" ");
					ob[i] = null;
					break;
				default:
					throw new RuntimeException("解析excel失败!");
				}
			}
			//
			ReportBeanUtils.convertToBean(ob, bean);
			list.add((T) bean);
		}
		return "YES";
	}

	private static HSSFCellStyle getCellHeaderStyle(HSSFWorkbook wb) {
		// 创建header字体样式
		HSSFFont font = wb.createFont();
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		font.setColor(HSSFColor.DARK_BLUE.index);
		font.setFontHeight((short) 250);
		// 创建header单元格样式
		HSSFCellStyle style = wb.createCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		style.setFont(font);// 设置字体
		return style;
	}

	private static HSSFCellStyle getCellDataStyle(HSSFWorkbook wb) {
		// 创建data单元格样式
		HSSFCellStyle styledata = wb.createCellStyle();
		styledata.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		styledata.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		// styledata.setWrapText(true);// 自动换行
		return styledata;
	}

	public static void download(HttpServletResponse response, String filePath,
			String fileName) throws IOException {
		FileInputStream fis = null;
		OutputStream os = null;
		try {
			fis = new FileInputStream(filePath);
			os = response.getOutputStream();// 取得输出流
			response.reset();// 清空输出流
			response.setHeader("Pragma", "No-cache");
			response.setHeader("Cache-Control", "no-cache");
			response.setDateHeader("Expires", 0);
			response.setContentType("application/x-msdownload;charset=utf-8");
			response.setHeader("Content-disposition", "attachment; filename="
					+ URLEncoder.encode(fileName, "UTF-8"));// 设定输出文件头
			byte[] mybyte = new byte[8192];
			int len = 0;
			while ((len = fis.read(mybyte)) != -1) {
				os.write(mybyte, 0, len);
			}
			os.close();
		} catch (IOException e) {
			throw e;
		}
	}

	public static void main(String[] args) {
		double ob = 12.00;
	}
}
